package attend.dao.BDADM;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import util.DBConnection;
import exception.BaseException;


/**
 * 파일명 : BDADM_AuthDAO.java
 * 관리자>권한관리
 * 최초 작성일 : 2008/11/11
 * @author seo myeong seong
 * 
 */
/*
 * 수정 이력 : 2011-10-18 (최병옥)
 */
public class BDADM_AuthDAO extends DBConnection	{
	
	
	Connection 			con 		= null;
	PreparedStatement 	pstmt 		= null;
	ResultSet 			rs 			= null;
	StringBuffer 		strBuff 	= new StringBuffer();
	
	
	private List<HashMap<String, String>> list = null;
	private List<HashMap<String, String>> main_list = null; // 메인 메뉴 그룹을 저장
	private List<HashMap<String, String>> menu_list = null; // 메뉴 그룹을 저장
	
	int no = 0; // 폼에 자동증가될 번호
	private int 		result 			= 0;
	private int 		searchResult 	= 0; // 조회된 데이터가 있는지 검사
	private String 		num 			= null; // 폼에 자동증가될 번호(형변환)
	private String 		auth 			= null;
	private String 		reg_id 			= null;
	private String 		code_nm 		= null;
	private int 		rowCount 		= 0;		// 페이징
	private String 		menu_code 		= null;		
	
	public BDADM_AuthDAO() {}
	
	/**
	 * 권한코드 가져오기
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void searchAuth() throws BaseException, SQLException{
		try {
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
			//이부분은 공통코드에서 직무를 쿼리해온다.
			findQuery.append("				SELECT 	CD , CD_NM      							\n");
			findQuery.append("				FROM 	TB_COMM_CD      							\n");
			findQuery.append("				WHERE 	GROUP_CD	=	'107'						\n");
			findQuery.append("					AND CD			<>	'0'  						\n");
			findQuery.append("					AND USE_YN		=	'Y'							\n");
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();
			
			list = new ArrayList<HashMap<String, String>>();
			while(rs.next()){
				HashMap<String,String>  hashtable = new HashMap<String, String>();
				hashtable.put("code_fcd", rs.getString("CD"));
				hashtable.put("code_nm", rs.getString("CD_NM"));
				
				list.add(hashtable);
			}			
			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}
	
	/**
	 * 전체/조건 tot건수(중간프레임)
	 * @param code_fcd	-String-
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void searchInput_tot(String code_fcd) throws BaseException, SQLException{
		try {
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.append(" 				SELECT  COUNT(*) AS rowNum 										\n");
			findQuery.append(" 				FROM 	TB_EMP 													\n");
			findQuery.append(" 				WHERE POSITION='"+code_fcd+"' 										\n");
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				rowCount = rs.getInt("rowNum");
			}
			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}
	
	/**
	 * 선택한 관리자 권한별로 같은 권한을 가진사람들을 조회
	 * @param code_fcd
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void searchFcd(String code_fcd , int start_row , int end_row) throws BaseException, SQLException {
		
		try {
			
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
			//이부분을 사원정보를 조회하도록 변경한다. 조회하는 컬럼도 변경한다.
			findQuery.append("			SELECT *                                                                                                  		\n");
			findQuery.append("			FROM                                                                                                      		\n");
			findQuery.append("			(                                                                                                         		\n");
			findQuery.append("			        SELECT                                                                                            		\n");
			findQuery.append("					ROW_NUMBER() OVER(ORDER BY EMP_NO) AS RowNum,                                             				\n");
			findQuery.append("					EMP_NO , EMP_NM , USER_ID , USER_PWD , RANK_NM , POSITION , APPLY_DT , RETIRE_DT , WORK_YN				\n");
			findQuery.append("				FROM                                                                                              			\n");
			findQuery.append("					TB_EMP                                                                                    				\n");
			findQuery.append("				WHERE POSITION='"+code_fcd+"'                                                                             	\n");
			findQuery.append("			)	AA                                                                                                			\n");
			findQuery.append("			WHERE  RowNum BETWEEN " + start_row + " AND " + end_row + "                                                                             				\n");

			//EMP_NO      FK_DEPT_NO  EMP_NM               USER_ID    USER_PWD   RANK_NM    POSITION   APPLY_DT RETIRE_DT WORK_YN
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();
			
			list = new ArrayList<HashMap<String, String>>();
			
			no = 0;
			
			while(rs.next()){
				no = no+1;
				num = Integer.toString(no);
				HashMap<String,String>  hashtable = new HashMap<String, String>();
				hashtable.put("num"			, num);
				hashtable.put("m_id"		, rs.getString("EMP_NO"));
				hashtable.put("user_nm"		, rs.getString("EMP_NM"));
				hashtable.put("id_no"		, rs.getString("USER_ID"));
				hashtable.put("pswd"		, rs.getString("USER_PWD"));
				hashtable.put("cel"			, rs.getString("RANK_NM"));
				hashtable.put("tel"			, rs.getString("POSITION"));
				hashtable.put("email"		, rs.getString("APPLY_DT"));
				hashtable.put("auth"		, rs.getString("RETIRE_DT"));
				hashtable.put("use_yn"		, rs.getString("WORK_YN"));
				
				auth 	= rs.getString("POSITION");
				reg_id 	= "IP";
				
				list.add(hashtable);
				
//				 이 메소스를 사용하는 action.java의 메소드를 확인하고 jsp에서 사용되는 값을 위에서 변경한 쿼리에 맞게 여기도 수정한다.  
			}				
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}
	
	/**
	 * sub 메뉴 조회 (main 메뉴에 사용)
	 * @param menu
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void searchMenu2( String position) throws BaseException, SQLException {
		try {
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
					
			findQuery.append("SELECT                                                                               				\n");
			findQuery.append("		A.MENU_GROUP_SORT,A.MENU_NM , A.MENU_URL , A.MENU_CODE , A.REG_ID , B.ACCESS_YN , A.MENU_SORT             \n");   				
			findQuery.append("FROM                                                                                 				\n");
			findQuery.append("(                                                                                    				\n");
			findQuery.append("		SELECT                                                                       				\n");
			findQuery.append("				MENU_NM , MENU_GROUP_SORT , MENU_URL , MENU_CODE , REG_ID , MENU_SORT	, MENU_GROUP ,USE_YN			\n");
			findQuery.append("		FROM                                                                         				\n");
			findQuery.append("				ADMIN_MENU_TB                                                        				\n");
			findQuery.append("		WHERE                                                                        				\n");
			findQuery.append("				MENU_SORT       !=      0                                            				\n");
			findQuery.append(")       A       ,                                                                    				\n");
			findQuery.append("(                                                                                    				\n");
			findQuery.append("		SELECT                                                                       				\n");
			findQuery.append("				MENU_URL , AUTH , ACCESS_YN                                          				\n");
			findQuery.append("		FROM                                                                         				\n");
			findQuery.append("				ADMIN_MENU_AUTH_TB                                                   				\n");
			findQuery.append(")       B                                                                            				\n");
			findQuery.append("WHERE   B.AUTH          =       '"+position+"'                                       				\n");
			findQuery.append("AND     A.MENU_CODE     =       B.MENU_URL                 \n");
			findQuery.append("AND 	B.ACCESS_YN = 'Y'\n");
			findQuery.append("AND 	A.USE_YN = 'Y'                          				\n");
			findQuery.append("ORDER BY    A.MENU_GROUP_SORT ASC    ,        A.MENU_SORT ASC    \n");
			
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();
			
			list = new ArrayList<HashMap<String, String>>();
			
			while(rs.next()) {
				
				searchResult ++;
				
				HashMap<String,String>  hashtable = new HashMap<String, String>();
				
				hashtable.put("menu_group_sort"		, rs.getString("MENU_GROUP_SORT"));
				hashtable.put("menu_nm"				, rs.getString("MENU_NM"));
				hashtable.put("menu_url"			, rs.getString("MENU_URL"));
				hashtable.put("menu_code"			, rs.getString("MENU_CODE"));
				hashtable.put("reg_id"				, rs.getString("REG_ID"));
				hashtable.put("access_yesno"		, rs.getString("ACCESS_YN"));
				hashtable.put("menu_sort"		, rs.getString("MENU_SORT"));
				
				if(rs.getString("ACCESS_YN").equals("Y")){
					hashtable.put("access_yn","허용" );
				}else{
					hashtable.put("access_yn","불가" );
				}
				list.add(hashtable);
			}	
			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}

	/**
	 * 페이지 라디오버튼 클릭시 하단데이터 조회
	 * @param menu
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void searchMenu( String position, String menu_code) throws BaseException, SQLException {
		try {
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
			
			findQuery.append("			SELECT                                                                               				\n");
			findQuery.append("			       A.MENU_GROUP_SORT, A.MENU_SORT, A.MENU_NM , A.MENU_URL , A.MENU_CODE , A.REG_ID , B.ACCESS_YN                				\n");
			findQuery.append("			FROM                                                                                 				\n");
			findQuery.append("			(                                                                                    				\n");
			findQuery.append("			        SELECT                                                                       				\n");
			findQuery.append("			                MENU_NM , MENU_GROUP_SORT , MENU_URL , MENU_CODE , REG_ID , MENU_SORT				\n");
			findQuery.append("			        FROM                                                                         				\n");
			findQuery.append("			                ADMIN_MENU_TB                                                        				\n");
//			findQuery.append("			        WHERE                                                                        				\n");
//			findQuery.append("			                MENU_SORT       !=      0                                            				\n");
			findQuery.append("			)       A       ,                                                                    				\n");
			findQuery.append("			(                                                                                    				\n");
			findQuery.append("			        SELECT                                                                       				\n");
			findQuery.append("			                MENU_URL , AUTH , ACCESS_YN                                          				\n");
			findQuery.append("			        FROM                                                                         				\n");
			findQuery.append("			                ADMIN_MENU_AUTH_TB                                                   				\n");
			findQuery.append("			)       B                                                                            				\n");
			findQuery.append("			WHERE   B.AUTH          =       '"+position+"'                                               		\n");
			findQuery.append("			AND  	B.MENU_URL      like       '"+menu_code+"'                                               	\n");
			findQuery.append("			AND     A.MENU_CODE     =       B.MENU_URL                                           				\n");
			findQuery.append("			ORDER BY     A.MENU_GROUP_SORT ,  A.MENU_SORT                                                  		\n");
			
			//findQuery.append("				 AND A.MENU_GROUP_SORT	=	'"+menu+"'																							\n");
			
			
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();
			
			list = new ArrayList<HashMap<String, String>>();
			
			while(rs.next()) {
				
				searchResult ++;
				
				HashMap<String,String>  hashtable = new HashMap<String, String>();
				
				hashtable.put("cnt"					, Integer.toString(rs.getRow())); // count 추가 (최병옥)
				hashtable.put("menu_group_sort"		, rs.getString("MENU_GROUP_SORT"));
				hashtable.put("menu_sort"			, rs.getString("MENU_SORT"));
				hashtable.put("menu_nm"				, rs.getString("MENU_NM"));
				hashtable.put("menu_url"			, rs.getString("MENU_URL"));
				hashtable.put("menu_code"			, rs.getString("MENU_CODE"));
				hashtable.put("reg_id"				, rs.getString("REG_ID"));
				hashtable.put("access_yesno"		, rs.getString("ACCESS_YN"));
				
				if(rs.getString("ACCESS_YN").equals("Y")){
					hashtable.put("access_yn","허용" );
				}else{
					hashtable.put("access_yn","불가" );
				}
				list.add(hashtable);
			}	
			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}
	
	/**
	 * 
	* <PRE>
	* 1. MethodName : search_main_group
	* 2. ClassName  : BDADM_AuthDAO
	* 3. Comment   : 메인메뉴 그룹을 select
	* 4. 작성자    : 최병옥
	* 5. 작성일    : 2011.10. 21. 오후 5:34:29
	* </PRE>
	*   @return void
	*   @throws BaseException
	*   @throws SQLException
	 */
	public void search_main_group() throws BaseException, SQLException{
		try {
			con = getDataSource().getConnection();
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
			
			findQuery.append("SELECT MENU_GROUP, MENU_GROUP_SORT 							\n");
			findQuery.append(" FROM ADMIN_MENU_TB WHERE MENU_SORT =  '0'					\n");
			findQuery.append("order by MENU_GROUP_SORT										\n");
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();			
			
			main_list = new ArrayList<HashMap<String, String>>();
			
			while(rs.next()) {
			
				searchResult ++;
				
				HashMap<String,String>  hashtable = new HashMap<String, String>();
				
			
				hashtable.put("menu_group"				, rs.getString("MENU_GROUP"));
				hashtable.put("menu_sort_group"			, rs.getString("MENU_GROUP_SORT"));
				
				main_list.add(hashtable);
			}
		} finally {
			close(rs);
			close(pstmt);
			close(con); 	
		}
		
	}
	
	/**
	 * 
	* <PRE>
	* 1. MethodName : update_auth
	* 2. ClassName  : BDADM_AuthDAO
	* 3. Comment   : 메뉴권한 update query (미작성) 
	* 4. 작성자    : 최병옥
	* 5. 작성일    : 2011. 10. 21. 오후 5:19:31
	* </PRE>
	*   @return void
	*   @param menu_code
	*   @param auth
	*   @throws BaseException
	*   @throws SQLException
	 */
	public void update_auth(String menu_code , String auth) throws BaseException, SQLException{
		try{			
			con = getDataSource().getConnection();
				StringBuffer findQuery = new StringBuffer();
				
				findQuery.setLength(0);
				
				findQuery.append("				qeury 추가 !												\n");
				
				System.out.println("-----------------------------------------------------------------------------");
				System.out.println(findQuery.toString());
				System.out.println("-----------------------------------------------------------------------------");
				
				pstmt = con.prepareStatement(findQuery.toString());
				rs = pstmt.executeQuery();			
				
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}		
	}
	
	
	/**
	 * 선택한 관리자명 가져오기
	 * @param code_fcd
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void searchNm(String code_fcd) throws BaseException, SQLException{
		try {
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
			
			findQuery.append("				SELECT										\n");
			findQuery.append("				 CD_NM										\n");
			findQuery.append("				 FROM TB_COMM_CD							\n");
			findQuery.append("				 WHERE cd = '"+code_fcd+"'					\n");
			
// 공통코드에서 해당  코드의 네임을 조회한다. -> 특별히 수정할 필요없을 듯
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				code_nm = rs.getString("CD_NM");
			}			
			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}
	
	/**
	 * 다중선택시 업데이트
	 * @param url_multi_data
	 * @param auth
	 * @param access_yn
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void updateByMulti(String url_multi_data , String select , String access_yn) throws BaseException, SQLException{
		try{			
			con = getDataSource().getConnection();
				StringBuffer findQuery = new StringBuffer();
				
				findQuery.setLength(0);
				
				findQuery.append("				UPDATE ADMIN_MENU_AUTH_TB SET												\n");
				findQuery.append("				ACCESS_YN		=	'"+access_yn+"',	LAST_MODIFIED=getdate()				\n");
				findQuery.append("				WHERE MENU_URL	=	'"+url_multi_data+"'									\n");	
				findQuery.append("				AND AUTH		=	'"+select+"'												\n");
				
				System.out.println("-----------------------------------------------------------------------------");
				System.out.println(findQuery.toString());
				System.out.println("-----------------------------------------------------------------------------");
				
				pstmt = con.prepareStatement(findQuery.toString());
				result = pstmt.executeUpdate();			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}
	
	/**
	 * 하나선택시 업데이트
	 * @param url_multi_data
	 * @param auth
	 * @param access_yn
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void updateByOne(String menu_url , String select , String access_yn , String id) throws BaseException, SQLException{
		
		try {
			con = getDataSource().getConnection();
			
			StringBuffer findQuery = new StringBuffer();
			
			findQuery.setLength(0);
			
			findQuery.append("				UPDATE ADMIN_MENU_AUTH_TB SET								\n");
			findQuery.append("				ACCESS_YN		='"+access_yn+"',REG_ID='"+id+"'			\n");
			findQuery.append("				WHERE MENU_URL	='"+menu_url+"'								\n");
			findQuery.append("				AND AUTH		='"+select+"'									\n");
			
			System.out.println("-----------------------------------------------------------------------------");
			System.out.println(findQuery.toString());
			System.out.println("-----------------------------------------------------------------------------");
			
			pstmt = con.prepareStatement(findQuery.toString());
			
			result = pstmt.executeUpdate();
			
		} finally {
			close(rs);
			close(pstmt);
			close(con); 			
		}
	}

//////////// getter,setter //////////////////////////////////////////////
	public List<HashMap<String, String>> getList() {
		return list;
	}

	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}

	public int getResult() {
		return result;
	}

	public void setResult(int result) {
		this.result = result;
	}

	public String getNum() {
		return num;
	}

	public void setNum(String num) {
		this.num = num;
	}

	public String getAuth() {
		return auth;
	}

	public void setAuth(String auth) {
		this.auth = auth;
	}

	public int getSearchResult() {
		return searchResult;
	}

	public void setSearchResult(int searchResult) {
		this.searchResult = searchResult;
	}

	public String getCode_nm() {
		return code_nm;
	}

	public void setCode_nm(String code_nm) {
		this.code_nm = code_nm;
	}

	public String getReg_id() {
		return reg_id;
	}

	public void setReg_id(String reg_id) {
		this.reg_id = reg_id;
	}

	public int getRowCount() {
		return rowCount;
	}

	public void setRowCount(int rowCount) {
		this.rowCount = rowCount;
	}

	public String getMenu_code() {
		return menu_code;
	}

	public void setMenu_code(String menu_code) {
		this.menu_code = menu_code;
	}

	public List<HashMap<String, String>> getMain_list() {
		return main_list;
	}

	public void setMain_list(List<HashMap<String, String>> main_list) {
		this.main_list = main_list;
	}

	public List<HashMap<String, String>> getMenu_list() {
		return menu_list;
	}

	public void setMenu_list(List<HashMap<String, String>> menu_list) {
		this.menu_list = menu_list;
	}
	
	
}
